﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace TorneoLoL
{
    public partial class FormTeams : Form
    {
        Conexion conexion = new Conexion();
        DataSet ds;
        String ConsultaSQL;

        public FormTeams()
        {
            InitializeComponent();
            this.Text = "Teams";
            this.FormBorderStyle = FormBorderStyle.None;
            this.WindowState = FormWindowState.Maximized;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ds = new DataSet();
            ConsultaSQL = "select Teams.T_NombreTeam as [Nombre Team], " +
                "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID";

            conexion.consultarDataSet( ref ds, "Teams", ConsultaSQL );
            grdTeams.DataSource = ds.Tables["Teams"];

            iniciarControles();
        }

        private void iniciarControles()
        {
            txtANombre.Enabled = false;
            txtCoach.Enabled = false;
            txtNombre.Enabled = false;
            txtJugador1.Enabled = false;
            txtJugador2.Enabled = false;
            txtJugador3.Enabled = false;
            txtJugador4.Enabled = false;
            txtJugador5.Enabled = false;


            CargarComboNumerico(ref cmbCantPartidas);
            CargarComboNumerico(ref cmbIdTeam);
            CargarComboTexto(ref cmbAcronTeam);
            CargarComboTexto(ref cmbPais);

        }

        private void CargarComboTexto(ref ComboBox NombreCombo)
        {
            NombreCombo.Items.Add("");
            NombreCombo.Items.Add("Comienza con:");
            NombreCombo.Items.Add("Termina en:");
            NombreCombo.Items.Add("Contiene:");
            NombreCombo.Items.Add("Es igual a:");
        }

        private void CargarComboNumerico(ref ComboBox NombreCombo)
        {
            NombreCombo.Items.Add("");
            NombreCombo.Items.Add("Igual a:");
            NombreCombo.Items.Add("Mayor a:");
            NombreCombo.Items.Add("Menor a:");
        }

        private void btnQuitarFiltro_Click( object sender, EventArgs e )
        {
            grdTeams.DataSource = null;
            ds = new DataSet();
            ConsultaSQL = "select Teams.T_NombreTeam as [Nombre Team], " +
                "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID ";
            conexion.consultarDataSet( ref ds, "Teams", ConsultaSQL );
            grdTeams.DataSource = ds.Tables["Teams"];
        }

        private void btnFiltrar_Click( object sender, EventArgs e )
        {
            grdTeams.DataSource = null;
            ds = new DataSet();
            ConsultaSQL = "select Teams.T_NombreTeam as [Nombre Team], " +
                "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID ";

            if(cmbIdTeam.Text != String.Empty && cmbIdTeam.FindString( cmbIdTeam.Text ) != -1 && txtIdTeam.Text != String.Empty)
            {
                switch(cmbIdTeam.Text)
                {
                    case "Igual a:":
                        ConsultaSQL += "where T_IdTeam = " + txtIdTeam.Text.ToString();
                        break;

                    case "Mayor a:":
                        ConsultaSQL += "where T_IdTeam > " + txtIdTeam.Text.ToString();
                        break;

                    case "Menor a:":
                        ConsultaSQL += "where T_IdTeam < " + txtIdTeam.Text.ToString();
                        break;
                }
            }

            if(cmbPais.Text != String.Empty && cmbPais.FindString( cmbPais.Text ) != -1 && txtPais.Text != String.Empty)
            {
                if(ConsultaSQL != "select Teams.T_NombreTeam as [Nombre Team], " +
                    "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                    "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                    "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID ")
                    
                    ConsultaSQL += " and ";
                else
                    ConsultaSQL += "where ";
                switch(cmbPais.Text)
                {
                    case "Comienza con:":
                        ConsultaSQL += "P_Nombre like '" + txtPais.Text.ToString() + "%'";
                        break;

                    case "Termina en:":
                        ConsultaSQL += "P_Nombre like '%" + txtPais.Text.ToString() + "'";
                        break;

                    case "Contiene:":
                        ConsultaSQL += "P_Nombre like '%" + txtPais.Text.ToString() + "%'";
                        break;

                    case "Es igual a:":
                        ConsultaSQL += "P_Nombre = '" + txtPais.Text.ToString() + "'";
                        break;
                }
            }

            if(cmbAcronTeam.Text != String.Empty && cmbAcronTeam.FindString( cmbAcronTeam.Text ) != -1 && txtATeam.Text != String.Empty)
            {
                if(ConsultaSQL != "select Teams.T_NombreTeam as [Nombre Team], " +
                    "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                    "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                    "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID ")
                   
                    ConsultaSQL += " and ";
                else
                    ConsultaSQL += "where ";
                switch(cmbAcronTeam.Text)
                {
                    case "Comienza con:":
                        ConsultaSQL += "T_NombreTeam like '" + txtATeam.Text.ToString() + "%'";
                        break;

                    case "Termina en:":
                        ConsultaSQL += "T_NombreTeam like '%" + txtATeam.Text.ToString() + "'";
                        break;

                    case "Contiene:":
                        ConsultaSQL += "T_NombreTeam like '%" + txtATeam.Text.ToString() + "%'";
                        break;

                    case "Es igual a:":
                        ConsultaSQL += "T_NombreTeam = '" + txtATeam.Text.ToString() + "'";
                        break;
                }
            }

            if(cmbCantPartidas.Text != String.Empty && cmbCantPartidas.FindString( cmbCantPartidas.Text ) != -1 && txtTPartidas.Text != String.Empty)
            {
                if(ConsultaSQL != "select Teams.T_NombreTeam as [Nombre Team], " +
                    "Teams.T_NombreCompleto as [Nombre Completo], Paises.P_Nombre as [País], " +
                    "Coaches.C_Nombre as [Nombre Coach], Coaches.C_Nick as [Nick Coach] " +
                    "from Teams inner join Coaches on T_idCoach = C_Id inner join Paises on T_Pais = P_ID ")
                    
                    ConsultaSQL += " and ";
                else
                    ConsultaSQL += "where ";
                switch(cmbCantPartidas.Text)
                {
                    case "Igual a:":
                        ConsultaSQL += "T_TotalEncuentros = " + txtTPartidas.Text.ToString();
                        break;

                    case "Mayor a:":
                        ConsultaSQL += "T_TotalEncuentros > " + txtTPartidas.Text.ToString();
                        break;

                    case "Menor a:":
                        ConsultaSQL += "T_TotalEncuentros < " + txtTPartidas.Text.ToString();
                        break;
                }
            }
            conexion.consultarDataSet( ref ds, "Teams", ConsultaSQL );
            grdTeams.DataSource = ds.Tables["Teams"];
        }

        private void grdTeams_RowHeaderMouseClick( object sender, DataGridViewCellMouseEventArgs e )
        {
            String aux;
            int i = 0;
            try
            {

                txtNombre.Text = grdTeams["Nombre Completo", grdTeams.CurrentRow.Index].Value.ToString();
                txtANombre.Text = grdTeams["Nombre Team", grdTeams.CurrentRow.Index].Value.ToString(); ;

                ds = new DataSet();
                txtCoach.Text = grdTeams["Nick Coach", grdTeams.CurrentRow.Index].Value.ToString();
                try
                {

                    conexion.consultarDataSet( ref ds, "Jugadores", "select J_Nick from Jugadores inner join Teams on J_IdTeam = T_IdTeam where T_NombreTeam = '" + grdTeams["Nombre Team", grdTeams.CurrentRow.Index].Value.ToString() + "'" );
                    aux = ds.Tables["Jugadores"].Rows[i][0].ToString();
                    if(aux != null)
                    {
                        txtJugador1.Text = aux;
                        i++;
                        aux = ds.Tables["Jugadores"].Rows[i][0].ToString();
                        if(aux != null)
                        {
                            txtJugador2.Text = aux;
                            i++;
                            aux = ds.Tables["Jugadores"].Rows[i][0].ToString();
                            if(aux != null)
                            {
                                txtJugador3.Text = aux;
                                i++;
                                aux = ds.Tables["Jugadores"].Rows[i][0].ToString();
                                if(aux != null)
                                {
                                    txtJugador4.Text = aux;
                                    i++;
                                    aux = ds.Tables["Jugadores"].Rows[i][0].ToString();
                                    if(aux != null)
                                        txtJugador5.Text = aux;
                                }
                            }
                        }
                    }
                }
                catch(System.Exception err)
                {
                    MessageBox.Show( err.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error );
                }

            }
            catch(System.ArgumentException err)
            {
                MessageBox.Show( err.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error );
            }
        }
    }
}
